﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections;
using System.Collections.ObjectModel;
using SYcwpb.Models;
using SYcwpb.Helpers;
using System.Data;
using System.Data.SqlClient;


namespace SYcwpb.Access
{
    class DriverTeamAccess
    {
        SqlDataLayer sqlHelper = new SqlDataLayer();
        private int line;

        public Dictionary<string, string> CallstateDictionary;

        public DriverTeamAccess(int line)
        {
            this.line = line;
            CallstateDictionary = new Dictionary<string, string>();
            CallstateDictionary.Add("0", "等待");
            CallstateDictionary.Add("S", "排队");
            CallstateDictionary.Add("3", "无人应答");
            CallstateDictionary.Add("5", "忙线");
            CallstateDictionary.Add("1", "线路异常");
            CallstateDictionary.Add("2", "未确认");
            CallstateDictionary.Add("7", "异常");
            CallstateDictionary.Add("Conn", "拨号接通");
        }

        /// <summary>
        /// 初始化参数
        /// </summary>
        public void GetConfig(ref Dictionary<string, string> config)
        {
            string sql_getconfig = "select * from ConfigSetting where cfg_view = 'DriverTeamView' and cfg_line = " + line;
            DataTable configDT = sqlHelper.SelectDt(sql_getconfig);
            if (null != configDT && configDT.Rows.Count > 0)
            {
                for (int i = 0; i < configDT.Rows.Count; i++)
                {
                    string key = configDT.Rows[i]["cfg_key"].ToString();
                    string value = configDT.Rows[i]["cfg_value"].ToString();
                    config.Add(key, value);
                }

            }
        }

        /// <summary>
        /// 返回早、白、夜、休 分别对应的司机队伍
        /// </summary>
        /// <returns></returns>
        public DataTable GetGroupToTeam()
        {
            string sql_get = "select ctn_cls_id as zbyx ,ctn_team as team from ClassTurn where line = " + line;
            return sqlHelper.SelectDt(sql_get);
        }

        public bool CancelShowRemind(int id)
        {
            string updateScript = "update inform set isshow=0 where Idd=" + id;
            return sqlHelper.ExecuteSql(updateScript)>0?true:false;
        }

        /// <summary>
        /// 选出需要提醒的司机
        /// </summary>
        /// <returns></returns>
        public DataTable GetDriverNeedRemind()
        {
            DataTable dt = null;
            //string sql_remindingDriver = "select brm_br_name as driverId,brm_username as driverName from BedRoomMaps br";

            string sql_remindingDriver = "SELECT Idd,Callvoice,PhoneNo,Callstate,uno  FROM Inform " +
                "  where Calltimes>=3 and Callstate<>'Conn' and deleted!=1 and (isshow is null or isshow=1) and CONVERT(varchar(100), Begindate, 23)='" + DateTime.Now.ToString("yyyy-MM-dd") + 
                "' and PhoneNo IN (SELECT br_phoneno FROM BedRooms WHERE br_line=" + line + ")";
            dt = sqlHelper.SelectDt(sql_remindingDriver);
            dt.Columns.Add(new DataColumn("CallstateDesc"));
            string roomNo = "(";
            foreach (DataRow row in dt.Rows)
            {
                if (row["Callstate"] != DBNull.Value)
                    row["CallstateDesc"] = CallstateDictionary[row["Callstate"].ToString()];
                if (row["uno"] != DBNull.Value)
                    roomNo += row["uno"].ToString() + ",";
            }
            if (dt!=null&&dt.Rows.Count > 0)
            {
                roomNo = roomNo.Remove(roomNo.Length - 1);
                roomNo += ")";
                string sqlScript = "select s.user_view_name,s.user_mobile_pn from BedRoomMaps as brm left join [system_user] as s on s.user_view_name=brm.brm_username "+
                    " where brm_date='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and  brm.brm_br_name in " + roomNo + " and user_view_name is not null";
                DataTable ddt = sqlHelper.SelectDt(sqlScript);
                foreach (DataRow row in dt.Rows)
                {
                    if (row["callvoice"] != DBNull.Value)
                    {
                        foreach (DataRow drow in ddt.Rows)
                        {

                            if (row["callvoice"].ToString().Contains(drow["user_view_name"].ToString()))
                            {
                                row["callvoice"] = row["callvoice"].ToString().Replace(drow["user_view_name"].ToString(), drow["user_view_name"].ToString() + "(" + drow["user_mobile_pn"].ToString() + ")");
                            }
                        }
                    }

                }
            }
            return dt;
        }


        /// <summary>
        /// 选出早班，白班，夜班，的司机
        /// </summary>
        /// <returns></returns>
        //public DataTable GetDriverInfoByClass()
        //{
        //    DataTable DriverDT = null;

        //    string sql_selectDriver = "SELECT  s.user_name , ( SELECT    CASE WHEN Dg.replaceDriver IS NULL THEN s.user_view_name ELSE s.user_view_name + '（替' + ( SELECT    user_view_name FROM      [System_User] WHERE     user_id = Dg.dgrp_user_id ) + '）' END ) AS user_view_name , s.user_pn , Ctn.ctn_cls_id AS cls_id , Cr.reptrain , Cr.attenPlace , Dg.attenTime AS planAtten , Dg.dgrp_attendtime AS actualAtten , Dg.dgrp_outtime AS outtime , Dcom.dcom_grp_type AS Gtype , s1.user_view_name AS PbyInfo , s2.user_view_name AS DTQ FROM    (SELECT  * FROM    DriverGroup WHERE   dgrp_date = '" + DateTime.Now.ToString("yyyy-MM-dd") + "' AND dgrp_user_id NOT IN ( SELECT  ID FROM  DriverCombineCrossingRoadPlane sd WHERE   sd.dcdr_userID IN ( SELECT  sr_SouceChanger FROM    SwitchedRecord s LEFT JOIN DriverCombineCrossingRoadPlane da ON da.dcdr_userID = s.sr_SouceApplicant AND s.sr_appdate = da.dcdr_date LEFT JOIN DriverCombineCrossingRoadPlane dc ON dc.dcdr_userID = s.sr_SouceApplicant AND s.sr_appdate = dc.dcdr_date AND da.dcdr_team = dc.dcdr_team WHERE   sr_appdate = '" + DateTime.Now.ToString("yyyy-MM-dd") + "' AND sr_chgdate IS NULL ) AND sd.dcdr_date = '" + DateTime.Now.ToString("yyyy-MM-dd") + "' )) Dg LEFT JOIN [System_User] s ON ( Dg.replaceDriver IS NULL AND Dg.dgrp_user_id = s.user_id ) OR Dg.replaceDriver = s.user_id LEFT JOIN [System_User] AS s1 ON s1.user_name = Dg.dgrp_pbyInfo LEFT JOIN [System_User] AS s2 ON s2.user_name = Dg.dgrp_dtq LEFT JOIN (SELECT * FROM DriverCombine WHERE dcom_date='" + DateTime.Now.ToString("yyyy-MM-dd") + "' ) Dcom ON Dg.dgrp_dcom_id = Dcom.ID LEFT JOIN ClassTurn Ctn ON ( Dcom.dcom_team = Ctn.ctn_team AND Dcom.dcom_lineid = Ctn.line ) LEFT JOIN CrossingRoad Cr ON Dcom.dcom_cr_number = Cr.ID WHERE Ctn.ctn_cls_id <> 1 AND Ctn.line ='" + line + "' ORDER BY cls_id ,planAtten;";

        //    DriverDT = sqlHelper.SelectDt(sql_selectDriver);
        //    return DriverDT;
        //}

        public DataTable GetDriverInfoByClass()
        {
            DataTable DriverDT = null;

            //string sql_selectDriver = "select S.user_name," +
            //    //" S.user_view_name," +
            //                        " (select case   when Dg.replaceDriver is null then  S.user_view_name  else " +
            //                        "S.user_view_name+'（替'+(select user_view_name from [system_user] where user_id=Dg.dgrp_user_id ) +'）' end ) as user_view_name," +
            //                        " S.user_pn," +
            //                        " Ctn.ctn_cls_id as cls_id," +
            //                        " Cr.reptrain ," +
            //                        " Cr.attenPlace, " +
            //    //" Cr.attenTime as planAtten," +     //计划时间替换为DriverGroup里的attenTime
            //                        " dg.attentime as planAtten," +
            //                        " Dg.dgrp_attendtime as actualAtten," +
            //                        " Dg.dgrp_outtime as outtime," +
            //                        " Dcom.dcom_grp_type as Gtype,s1.user_view_name as PbyInfo,s2.user_view_name as DTQ" +
            //                        " from " +
            //                        " DriverGroup Dg " +
            //                        " left join [system_user] S on (Dg.replaceDriver is null and Dg.dgrp_user_id = S.user_id) or  Dg.replaceDriver=S.user_id" +
            //    //融合MySql，user_id改为[user_name]
            //    //" left join [system_user] as s1 on s1.user_id=dg.dgrp_pbyinfo" +
            //    // " left join [system_user] as s2 on s2.user_id=dg.dgrp_dtq "+
            //                          " left join [system_user] as s1 on s1.user_name=dg.dgrp_pbyinfo" +
            //                         " left join [system_user] as s2 on s2.user_name=dg.dgrp_dtq " +
            //    //"left join [system_user] S on Dg.dgrp_user_id = S.user_id" +
            //                        " left join DriverCombine Dcom on Dg.dgrp_dcom_id = Dcom.ID" +
            //                        " left join ClassTurn Ctn on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
            //                        " left join CrossingRoad Cr on Dcom.dcom_cr_number = Cr.ID" +
            //                        " where  Ctn.ctn_cls_id <> 1 and Ctn.line = " + line + " and Dg.dgrp_user_id not in ( " +
            //                        "select ID from DriverCombineCrossingRoadPlane sd " +
            //                        "where sd.dcdr_userID in(" +
            //                        "select sr_SouceChanger from SwitchedRecord s " +
            //                        "left join DriverCombineCrossingRoadPlane da on da.dcdr_userID=s.sr_SouceApplicant and s.sr_appdate=da.dcdr_date " +
            //                        "left join DriverCombineCrossingRoadPlane dc on dc.dcdr_userID=s.sr_SouceApplicant and s.sr_appdate=dc.dcdr_date " +
            //                        "where sr_appdate='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and sr_chgdate is null and da.dcdr_team=dc.dcdr_team) and sd.dcdr_date='" + DateTime.Now.ToString("yyyy-MM-dd") + "' )" +
            //                         " order by cls_id,planAtten";
            string sqlUserid = "select sr_SouceChanger from SwitchedRecord s " +
                                "left join DriverCombineCrossingRoadPlane da on da.dcdr_userID=s.sr_SouceApplicant and s.sr_appdate=da.dcdr_date " +
                                "left join DriverCombineCrossingRoadPlane dc on dc.dcdr_userID=s.sr_SouceApplicant and s.sr_appdate=dc.dcdr_date " +
                                 "where sr_appdate='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and sr_chgdate is null and da.dcdr_team=dc.dcdr_team ";

            DataTable DriverUserid = sqlHelper.SelectDt(sqlUserid);
            string whereStr = string.Empty;
            if (DriverUserid.Rows.Count > 0)
            {

                for (int i = 0; i < DriverUserid.Rows.Count; i++)
                {
                    whereStr += "'" + DriverUserid.Rows[i]["sr_SouceChanger"].ToString() + "',";
                }

                //if (!string.IsNullOrEmpty(whereStr))
                //{

                //}
            }
            whereStr += "'0'";
            string sql_selectDriverNew = "select S.user_name," +
                //" S.user_view_name," +
                                               " (select case   when Dg.replaceDriver is null then  (case when dgo.IsTemp=1 then S.user_view_name+'(临时)' else S.user_view_name end)  else " +
                                               "S.user_view_name+'（替'+(select user_view_name from [system_user] where user_id=Dg.dgrp_user_id ) +'）' end ) as user_view_name," +
                                               " S.user_pn," +
                                               " Ctn.ctn_cls_id as cls_id," +
                                               " Cr.reptrain ," +
                                               " Cr.attenPlace, " +
                //" Cr.attenTime as planAtten," +     //计划时间替换为DriverGroup里的attenTime
                                               " dg.attentime as planAtten," +
                                               " dgo.OutTime as planOut," +
                                               " Dg.dgrp_attendtime as actualAtten," +
                                               " Dg.dgrp_outtime as outtime," +
                                               " Dcom.dcom_grp_type as Gtype,s1.user_view_name as PbyInfo,s2.user_view_name as DTQ" +
                                               " from " +
                                               " DriverGroup Dg " +
                                               " left join [DriverGroupOutTime] dgo on dgo.DGID=Dg.ID" +
                                               " left join [system_user] S on (Dg.replaceDriver is null and Dg.dgrp_user_id = S.user_id) or  Dg.replaceDriver=S.user_id" +
                //融合MySql，user_id改为[user_name]
                //" left join [system_user] as s1 on s1.user_id=dg.dgrp_pbyinfo" +
                // " left join [system_user] as s2 on s2.user_id=dg.dgrp_dtq "+
                                                 " left join [system_user] as s1 on s1.user_name=dg.dgrp_pbyinfo" +
                                                " left join [system_user] as s2 on s2.user_name=dg.dgrp_dtq " +
                //"left join [system_user] S on Dg.dgrp_user_id = S.user_id" +
                                               " left join DriverCombine Dcom on Dg.dgrp_dcom_id = Dcom.ID" +
                                               " left join ClassTurn Ctn on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                                               " left join CrossingRoad Cr on Dcom.dcom_cr_number = Cr.ID" +
                                               //" where  Ctn.ctn_cls_id <> 1 and Ctn.line = " + line + " and Dg.dgrp_user_id not in ( " +
                                               " where  Ctn.ctn_cls_id <> 1 and Ctn.line = " + line + " and NOT EXISTS ( " +
                                               "select ID from DriverCombineCrossingRoadPlane sd " +
                                               "where sd.dcdr_userID in(" + whereStr + ")" + " and sd.dcdr_date='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and Dg.dgrp_user_id=ID )" +
                                                 " order by cls_id,planAtten";
            DriverDT = sqlHelper.SelectDt(sql_selectDriverNew);
            return DriverDT;
        }

        public int GetDriverGrpType(string driverId, string date, string lineId)
        {
            string sql = " select dc.dcom_grp_type from DriverCombine as dc left join drivergroup as dg on dg.dgrp_dcom_id=dc.id  " +
                " where dg.dgrp_line="+lineId+
                " and dg.dgrp_date='" + date + "' and dg.dgrp_user_id='" + driverId + "'";
            return Convert.ToInt32(sqlHelper.GetSingle(sql));
        }
        /// <summary>
        /// 获取出勤司机的信息，包括本职出勤和代班出勤
        /// </summary>
        /// <param name="driverId"></param>
        /// <param name="date"></param>
        /// <param name="lineId"></param>
        /// <returns></returns>
        public DataTable GetDutyDriver(string dgid)
        {
            string sql = "select dg.*,s.user_name as dutyId,s1.user_name as replaceId from drivergroup as dg left join [system_user] as s on s.user_id=dg.dgrp_user_id " +
                " left join [system_user] as s1 on s1.user_id=dg.replacedriver " +
                " left join DriverCombine as dc on dc.id=dg.dgrp_dcom_id " +
                " where dg.id="+dgid;
            return sqlHelper.SelectDt(sql);
        }
        public DataTable GetDriverInfo(string tranId)
        {

            DataTable DriverDT = null;

            //以什么排序可能要调整一下，根据出勤时间？
            string SQL_selectDriver = "select D.dgrp_user_id as user_id,S.user_name as jobNum ," +
                                    "S.user_view_name as user_name," +
                                    " (select case   when D.replaceDriver is null then  ''  else " +
                                    "'替'+(select user_view_name from [system_user] where user_id=D.dgrp_user_id ) +'' end ) as replace_name," +
                                    "C.ctn_cls_id as cls_id," +
                                    " D.dgrp_srd_id as srd_id," +
                                    " Dr.dcom_dutynum as duty_num,Cr.number as cr_num, Dr.dcom_grp_type as grp_type,S.user_line as line,D.replaceDriver as replaceDriver ,D.ID as ID,Dr.dcom_NO" +
                                    " from DriverGroup D" +
                //" left join [system_user] S on D.dgrp_user_id = S.user_id" +
                                    " left join [system_user] S on (D.replaceDriver is null and D.dgrp_user_id = S.user_id) or  D.replaceDriver=S.user_id" +
                                    " left join DriverCombine Dr on D.dgrp_dcom_id = Dr.ID" +
                                    " left join CrossingRoad Cr on Dr.dcom_cr_number = Cr.ID" +
                                    " left join ClassTurn C on C.ctn_team = Dr.dcom_team and C.line = D.dgrp_line" +
                                    " where D.dgrp_line = " + line + "  order by Dr.dcom_NO";
            //and (D.replaceDriver is  null or D.dgrp_user_id in (select replaceDriver from DriverGroup dg where dg.dgrp_line= " + line + "  ))
            if (string.IsNullOrEmpty(tranId))
                DriverDT = sqlHelper.SelectDt(SQL_selectDriver);
            else
                DriverDT = sqlHelper.SelectDt(SQL_selectDriver,tranId);

            return DriverDT;
        }
        public DataTable GetDriverInfo()
        {
            return GetDriverInfo(null);
        }

        public DataTable GetDriverInfoByRest(string tranId)
        {
            DataTable DriverDT = null;

            string SQL_selectDriver = "select D.dgrp_user_id as user_id,S.user_name as jobNum ," +
                                  "S.user_view_name as user_name," +
                                  "' ' as replace_name," +
                                  "C.ctn_cls_id as cls_id," +
                                  " D.dgrp_srd_id as srd_id," +
                                  " Dr.dcom_dutynum as duty_num,Cr.number as cr_num, Dr.dcom_grp_type as grp_type,S.user_line as line,D.replaceDriver as replaceDriver ,D.ID as ID,Dr.dcom_NO" +
                                  " from DriverGroup D" +
                //" left join [system_user] S on D.dgrp_user_id = S.user_id" +
                                  " left join [system_user] S on  D.dgrp_user_id = S.user_id" +
                                  " left join DriverCombine Dr on D.dgrp_dcom_id = Dr.ID" +
                                  " left join CrossingRoad Cr on Dr.dcom_cr_number = Cr.ID" +
                                  " left join ClassTurn C on C.ctn_team = Dr.dcom_team and C.line = D.dgrp_line" +
                                  " where D.dgrp_line = " + line + " and ((dgrp_user_id not in (select ISNULL(replaceDriver,0) from DriverGroup) and replaceDriver is not null) or dgrp_dcom_id is  null) order by Dr.dcom_NO";

            //以什么排序可能要调整一下，根据出勤时间？
            //string SQL_selectDriver = " select * from DriverGroup where  (dgrp_user_id not in (select ISNULL(replaceDriver,0) from DriverGroup) and replaceDriver is not null) or dgrp_dcom_id is  null and dgrp_line="+line+"";
            //and (D.replaceDriver is  null or D.dgrp_user_id in (select replaceDriver from DriverGroup dg where dg.dgrp_line= " + line + "  ))
            if(string.IsNullOrEmpty(tranId))
                DriverDT = sqlHelper.SelectDt(SQL_selectDriver);
            else
                DriverDT = sqlHelper.SelectDt(SQL_selectDriver,tranId);

            return DriverDT;
        }
        public DataTable GetDriverInfoByRest()
        {
            return GetDriverInfoByRest(null);
        }

        /// <summary>
        /// 检查今天是否执行过班组轮换，是的话返回true,否返回false
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool CheckHasTurn()
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select count(*) from ReTurnRecord where re_date = '" + today + "' and re_line =" + line;

            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        /// <summary>
        /// 执行班组轮换，成功后，在ReTrunGroup中插入相应记录
        /// </summary>
        /// <returns></returns>
        public bool ExeTurnGroups(ref string error)
        {
            ArrayList sqlList = new ArrayList();

            ///班组轮换
            string sql_exeTurn = "update ClassTurn set ctn_cls_id = (ctn_cls_id + 1)%4 where line = " + line;

            ///更新DriverGroup里面的司机出勤信息或调班信息,将dgrp_date改为今天
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_setNull = "update DriverGroup set dgrp_dcom_id = null,dgrp_srd_id = null,dgrp_attendtime = null,dgrp_outtime = null,dgrp_room = null,dgrp_date='" + today + "' where dgrp_line = " + line;

            sqlList.Add(sql_exeTurn);
            sqlList.Add(sql_setNull);

            ///将DriverCombine对应的信息赋予相应司机
            string sql_update = "select D.ID as DgID ,D.dgrp_user_id ,Dcom.ID as DcomID" +
                                " from DriverGroup D left join DriverCombine Dcom on " +
                                " D.dgrp_user_id = Dcom.dcom_opdriver or D.dgrp_user_id = Dcom.dcom_monidriver" +
                                " where Dcom.ID is not null and D.dgrp_line = " + line;

            DataTable Dt = sqlHelper.SelectDt(sql_update);
            if (null == Dt || Dt.Rows.Count < 0)
                return false;

            for (int i = 0; i < Dt.Rows.Count; i++)
            {
                string sql = "update DriverGroup set dgrp_dcom_id = '" + Dt.Rows[i]["DcomID"].ToString() + "' where ID = '" + Dt.Rows[i]["DgID"].ToString() + "'";
                sqlList.Add(sql);
            }

            if (!sqlHelper.ExecuteSqlTran(sqlList))
            {
                error = "执行班组轮换失败！";
                return false;
            }

            //班组轮换成功后再调整司机的调班信息
            error = AddSwitchRecord(today);

            return string.IsNullOrEmpty(error);
        }

        /// <summary>
        /// 添加调班信息到DriverGroup表
        /// </summary>
        /// <returns></returns>
        public string AddSwitchRecord(string today)
        {
            ArrayList sqlList = new ArrayList();
            //检查今天的调班信息
            string sql_selectRecord = "select * from SwitchedRecord where sr_appdate > '" + today + "' or sr_chgdate > '" + today + "'";

            DataTable chgDT = sqlHelper.SelectDt(sql_selectRecord);
            if (chgDT != null && chgDT.Rows.Count > 0)
            {
                try
                {
                    for (int i = 0; i < chgDT.Rows.Count; i++)
                    {
                        int recordID = Convert.ToInt32(chgDT.Rows[i]["ID"]);
                        int app = Convert.ToInt32(chgDT.Rows[i]["sr_applicant"]);
                        int chg = Convert.ToInt32(chgDT.Rows[i]["sr_changer"]);
                        string appdate = Convert.ToString(chgDT.Rows[i]["sr_appdate"]);  //调换前日期
                        string chgdate = Convert.ToString(chgDT.Rows[i]["sr_chgdate"]); //调换后日期
                        string update1 = null;
                        string update2 = null;
                        if (today == appdate && appdate == chgdate)   //调班与还班是同一天
                        {
                            string app_dcomId = "";
                            string chg_dcomId = "";
                            string sql_getapp_dcomid = "select dgrp_dcom_id from DriverGroup where dgrp_user_id = " + app;
                            string sql_getchg_dcomid = "select dgrp_dcom_id from DriverGroup where dgrp_user_id = " + chg;
                            try
                            {
                                app_dcomId = Convert.ToString(sqlHelper.GetSingle(sql_getapp_dcomid));
                                chg_dcomId = Convert.ToString(sqlHelper.GetSingle(sql_getchg_dcomid));
                                if (string.IsNullOrEmpty(app_dcomId) && string.IsNullOrEmpty(chgdate))
                                    continue;
                                update1 = "update DriverGroup set dgrp_srd_id = " + recordID + ", dgrp_dcom_id = '" + chg_dcomId + "' where dgrp_user_id = " + app;
                                update2 = "update DriverGroup set dgrp_srd_id = " + recordID + ", dgrp_dcom_id = '" + app_dcomId + "' where dgrp_user_id = " + chg;
                            }
                            catch (System.Exception ex)
                            {

                            }
                        }
                        else if (today == appdate) //调班日期，申请者不上班，调换人上班
                        {
                            update1 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id = (select dgrp_dcom_id from DriverGroup where dgrp_user_id = " +
                                app + ") where dgrp_user_id = " + chg;  //调换人上班
                            update2 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id = null where dgrp_user_id = " + app; //申请人不上班
                        }
                        else if (today == chgdate) //还班日期  申请人上班，调换人不上班
                        {
                            update1 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id =(select dgrp_dcom_id from DriverGroup where dgrp_user_id = " +
                                chg + ") where dgrp_user_id = " + app;
                            update2 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id = null where dgrp_user_id = " + chg;
                        }
                        if (!string.IsNullOrEmpty(update1) && !string.IsNullOrEmpty(update2))
                        {
                            sqlList.Add(update1);
                            sqlList.Add(update2);
                        }
                    }
                    sqlHelper.ExecuteSqlTran(sqlList);
                }
                catch (System.Exception ex)
                {
                    string err = "调整调班信息时出现异常：" + ex.Message;
                    return err;
                }
            }
            return null;
        }

        public bool AddTrunGroupsRecord()
        {
            string sql_add = "insert into ReTurnRecord values('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + line + "')";
            return sqlHelper.ExecuteSql(sql_add) > 0;
        }

        /// <summary>
        /// 将昨日的出勤记录添加到 DriverAttendRecord
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool AddAttenRecord()
        {
            bool result = true;
            string sql_selelctRecord = "select" +
                                        " Dr.dgrp_user_id," +
                                        " [Post].post_name," +
                                        " Dr.dgrp_date," +
                                        " Dr.dgrp_line," +
                                        " Ctn.ctn_cls_id," +
                                        " Dcom.dcom_dutynum," +
                                        " Cr.ID as cr_ID," +
                                        " Dr.dgrp_attendtime," +
                                        " Dr.dgrp_outtime," +
                                        " Dcom.dcom_grp_type," +
                                        " Dr.dgrp_room" +
                                        " from DriverGroup Dr left join DriverCombine Dcom on (Dr.dgrp_dcom_id = Dcom.ID and Dr.dgrp_line = Dcom.dcom_lineid)" +
                                        " left join [system_user] on Dr.dgrp_user_id = user_id" +
                                        " left join Position [Post] on [Post].ID = user_post" +
                                        " left join ClassTurn Ctn on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                                        " left join CrossingRoad Cr on  (Dcom.dcom_cr_number = Cr.ID)" +
                                        " where Ctn.ctn_cls_id in (0,2,3) and Dr.dgrp_line = " + line;
            try
            {
                DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlBulkCopy bcp = new SqlBulkCopy(SqlDataLayer.ConnStr, SqlBulkCopyOptions.KeepIdentity);
                bcp.DestinationTableName = "DriverAttendRecord";
                bcp.BatchSize = 100;
                for (int i = 0; i < resultDT.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(resultDT);
            }
            catch (System.Exception ex)
            {
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 获取上一次执行编号循环的日期
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public string GetLastNumCycleDay()
        {
            string sql_getlastdate = "select top 1 re_date from ReNumRecord where re_line = " + line + " order by re_date desc";
            return Convert.ToString(sqlHelper.GetSingle(sql_getlastdate));
        }
        /// <summary>
        /// 执行编号循环
        /// </summary>
        /// <param name="line"></param>
        /// <param name="isTurnBackup">指示是否轮换备用司机</param>
        /// <returns></returns>
        public bool ExeNumCycle()
        {
            ArrayList sqlList = new ArrayList();

            int maxT1 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 1"));
            int maxT2 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 2"));
            int maxT3 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 3"));
            int maxT4 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 4"));

            //首先不考虑备班司机的轮换
            //string sql_updateAll = "update DriverCombine set dcom_dutynum = dcom_dutynum + 1 where dcom_grp_type<>4 and dcom_lineid = " + line; 
            string sql_updateAll = "update DriverCombine set dcom_dutynum = dcom_dutynum + 1 where dcom_lineid = " + line; //备用司机不轮换
            string sql_updateT1 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT1 + " where dcom_team = 1 and dcom_dutynum <>" + maxT1 + " and dcom_lineid =" + line;
            string sql_updateT2 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT2 + " where dcom_team = 2 and dcom_dutynum <>" + maxT2 + " and dcom_lineid =" + line;
            string sql_updateT3 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT3 + " where dcom_team = 3 and dcom_dutynum <>" + maxT3 + " and dcom_lineid =" + line;
            string sql_updateT4 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT4 + " where dcom_team = 4 and dcom_dutynum <>" + maxT4 + " and dcom_lineid =" + line;

            sqlList.Add(sql_updateAll);
            sqlList.Add(sql_updateT1);
            sqlList.Add(sql_updateT2);
            sqlList.Add(sql_updateT3);
            sqlList.Add(sql_updateT4);

            //更新dutynum与备班司机相同的那组为 (dutynum + 1) % Max

            //string sql_updateSame1 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT1 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = ("+
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //string sql_updateSame2 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT2 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = (" +
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //string sql_updateSame3 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT3 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = (" +
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //string sql_updateSame4 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT4 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = (" +
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //sqlList.Add(sql_updateSame1);
            //sqlList.Add(sql_updateSame2);
            //sqlList.Add(sql_updateSame3);
            //sqlList.Add(sql_updateSame4);

            return sqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 成功执行编号循环后添加记录到 ReNumRecord表
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool AddNumCycleRecord()
        {
            string sql_add = "insert into ReNumRecord values('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + line + "')";
            return sqlHelper.ExecuteSql(sql_add) > 0;
        }

        /// <summary>
        /// 将DriverCombine对应的信息赋予相应司机
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool UpdateDutyInfo()
        {
            string sql_update = "select D.ID as DgID ,D.dgrp_user_id ,Dcom.ID as DcomID" +
                                " from DriverGroup D left join DriverCombine Dcom on " +
                                " D.dgrp_user_id = Dcom.dcom_opdriver or D.dgrp_user_id = Dcom.dcom_monidriver" +
                                " where Dcom.ID is not null";


            return sqlHelper.ExecuteSql(sql_update) > 0;
        }

        /// <summary>
        /// 检查是否有安排时刻表到日期
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool CheckHasTimeTableToDate(ref int tableID, ref string tableName, string today)
        {
            //string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select TTd.Tableinfo_id,TIF.TableName" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = " + line + " and TTD.AssignDate = '" + today + "'";

            DataTable dt = sqlHelper.SelectDt(sql_check);
            if (null != dt && dt.Rows.Count > 0)
            {
                tableID = Convert.ToInt32(dt.Rows[0]["Tableinfo_id"]);
                tableName = dt.Rows[0]["TableName"].ToString();
            }

            return tableID == -1 ? false : true;
        }



        /// <summary>
        /// 检查是否有该时刻表对应的交路表信息
        /// </summary>
        /// <returns></returns>
        public bool CheckHasCrossRoad(int tableID)
        {
            string sql_check = "select count(*) from CrossingRoad where tableInfo_id = " + tableID + " and isused = 1";
            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        /// <summary>
        /// 今天是否已将交路表的信息刷新到DriverCombine表
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool CheckHasRefreshCrossRoad()
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select count(*) from CrossRoadRecord where re_date ='" + today + "' and line = " + line;
            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        /// <summary>
        /// 增加今天已安排交路到司机的记录到CrossRoadRecord表
        /// </summary>
        /// <returns></returns>
        public bool AddRecordToRefreshCrossRoad()
        {
            string sql_add = "insert into CrossRoadRecord values('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + line + "')";
            return sqlHelper.ExecuteSql(sql_add) > 0;
        }

        /// <summary>
        ///更新DriverCombine表中的相应的Crssroad字段，没完善。
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool RefreshCrossingRoad(int tableID, string tableName, ref string error)
        {
            bool result = false;

            //从交路表里选合适的交路，如果以后增加了备用，热备，调车等交路，需要更改筛选条件
            string crossRoadZB = "select ID,number from CrossingRoad where tableInfo_id = " + tableID + " and zby = 0 and isused = 1 and grp_id = 5 order by number";
            string crossRoadBB = "select ID,number from CrossingRoad where tableInfo_id = " + tableID + " and zby = 2 and isused = 1 and grp_id = 5 order by number";
            string crossRoadYB = "select ID,number from CrossingRoad where tableInfo_id = " + tableID + " and zby = 3 and isused = 1 and grp_id = 5 order by number";

            DataTable roadsZB = sqlHelper.SelectDt(crossRoadZB);
            DataTable roadsBB = sqlHelper.SelectDt(crossRoadBB);
            DataTable roadsYB = sqlHelper.SelectDt(crossRoadYB);

            //早班司机按夜班回库顺序 dcom_ybarrive 排序
            string teamZB = "select Dcom.ID as DcomID,Dcom.dcom_dutynum as duytnum,Dcom.dcom_team as team" +
                            " from DriverCombine Dcom left join ClassTurn Ctn" +
                            " on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                            " where Dcom.dcom_lineid = " + line + " and Ctn.ctn_cls_id = 0 and Dcom.dcom_grp_type = 5" +
                            " order by Dcom.dcom_ybarrive, Dcom.dcom_dutynum ";

            string teamBB = "select Dcom.ID as DcomID,Dcom.dcom_dutynum as duytnum,Dcom.dcom_team as team" +
                            " from DriverCombine Dcom left join ClassTurn Ctn" +
                            " on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                            " where Dcom.dcom_lineid = " + line + " and Ctn.ctn_cls_id = 2 and Dcom.dcom_grp_type = 5" +
                            " order by Dcom.dcom_dutynum ";

            string teamYB = "select Dcom.ID as DcomID,Dcom.dcom_dutynum as duytnum,Dcom.dcom_team as team" +
                            " from DriverCombine Dcom left join ClassTurn Ctn" +
                            " on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                            " where Dcom.dcom_lineid = " + line + " and Ctn.ctn_cls_id = 3 and Dcom.dcom_grp_type = 5" +
                            " order by Dcom.dcom_dutynum ";

            DataTable teamZBDT = sqlHelper.SelectDt(teamZB);
            DataTable teamBBDT = sqlHelper.SelectDt(teamBB);
            DataTable teamYBDT = sqlHelper.SelectDt(teamYB);

            if (roadsZB.Rows.Count > teamZBDT.Rows.Count)
            {
                string team = teamZBDT.Rows[0]["team"].ToString();
                error = string.Format("电客车司机 {0} 组的司机正线分组数:{1} 组，与时刻表:{2} 对应的早班正线交路数:{3} 不一致，请调整后重试！",
                    team, teamZBDT.Rows.Count, tableName, roadsZB.Rows.Count);
                return result;
            }
            if (roadsBB.Rows.Count > teamBBDT.Rows.Count)
            {
                string team = teamBBDT.Rows[0]["team"].ToString();
                error = string.Format("电客车司机 {0} 组的司机正线分组数:{1} 组，与时刻表:{2} 对应的白班正线交路数:{3} 不一致，请调整后重试！",
                    team, teamBBDT.Rows.Count, tableName, roadsBB.Rows.Count);
                return result;
            }
            if (roadsYB.Rows.Count > teamYBDT.Rows.Count)
            {
                string team = teamYBDT.Rows[0]["team"].ToString();
                error = string.Format("电客车司机 {0} 组的司机正线分组数:{1} 组，与时刻表:{2} 对应的夜班正线交路数:{3} 不一致，请调整后重试！",
                    team, teamYBDT.Rows.Count, tableName, roadsYB.Rows.Count);
                return result;
            }

            ArrayList SqlList = new ArrayList();

            //首先将DriverCombine的 dcom_cr_number 字段清空
            string sql_clear = "update DriverCombine set dcom_cr_number = null where dcom_lineid = " + line;
            SqlList.Add(sql_clear);

            if (
                !(refreshSqlList(roadsZB, teamZBDT, ref SqlList) &&
                    refreshSqlList(roadsBB, teamBBDT, ref SqlList) &&
                    refreshSqlList(roadsYB, teamYBDT, ref SqlList))
                )
            {
                return result;
                error = "执行交路更新出现异常！更新失败！";
            }

            //热备，备用，调车等特殊交路的处理
            string sql_specialgroup = "select" +
                            " Dcom.ID as Dcom_ID," +
                            " Cr.ID as Cr_ID," +
                            " Cr.zby" +
                            " from DriverCombine Dcom left join ClassTurn Ctn on Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line" +
                            " left join CrossingRoad Cr on Dcom.dcom_grp_type = Cr.grp_id and Ctn.ctn_cls_id = Cr.zby" +
                            " where tableInfo_id = '" + tableID + "' and isused = 1 and Dcom.dcom_lineid = " + line + " and Dcom.dcom_grp_type <> 5";
            DataTable specialDT = sqlHelper.SelectDt(sql_specialgroup);
            AddSpecialGroup(specialDT, ref SqlList);


            //先将夜班的清理掉
            string sql_clearybarrive = "update DriverCombine set dcom_ybarrive = null";
            SqlList.Add(sql_clearybarrive);

            string ybIndex = "select Dcom.ID as Dcom_ID ," +
                            " Dcom.dcom_team as Dcon_team, " +
                            " CT.ctn_cls_id as CT_Class ," +
                            " Cr.number as Cr_num ," +
                            " Cr.offTime as Cr_offTime," +
                            " Cr.zby" +
                            " from DriverCombine Dcom " +
                            " left join ClassTurn CT on Dcom.dcom_team = CT.ctn_team and Dcom.dcom_lineid = CT.line" +
                            " left join CrossingRoad Cr on Dcom.dcom_cr_number = Cr.ID" +
                            " where Dcom.dcom_lineid = " + line + " and CT.ctn_cls_id = 3 order by Cr_offTime";

            DataTable ybArriveDT = sqlHelper.SelectDt(ybIndex);

            UpdateYbOffTime(ybArriveDT);

            UpdateYbArriverInfo(ybArriveDT, ref SqlList);

            result = sqlHelper.ExecuteSqlTran(SqlList);

            return result;
        }

        /// <summary>
        /// 特殊班组司机的交路处理
        /// </summary>
        private void AddSpecialGroup(DataTable specialDT, ref ArrayList sqlList)
        {
            if (null == specialDT || specialDT.Rows.Count < 1)
                return;

            for (int i = 0; i < specialDT.Rows.Count; i++)
            {
                string roadID = specialDT.Rows[i]["Cr_ID"].ToString();
                string teamID = specialDT.Rows[i]["Dcom_ID"].ToString();

                string sql = "update DriverCombine set dcom_cr_number = '" + roadID + "' where ID = '" + teamID + "'";

                sqlList.Add(sql);
            }
        }


        /// <summary>
        /// 12点后回库的司机，例如 00:03:40 此类的司机应该加上24小时
        /// </summary>
        private void UpdateYbOffTime(DataTable ybArriveDT)
        {
            if (null == ybArriveDT || ybArriveDT.Rows.Count < 1)
                return;

            string time = "03:00:00";  //三点前的都应该加上24小时
            int dayTime = 24 * 60 * 60;
            for (int i = 0; i < ybArriveDT.Rows.Count; i++)
            {
                string str_cur = ybArriveDT.Rows[i]["Cr_offTime"].ToString();
                if (str_cur.CompareTo(time) < 1)
                {
                    int int_cur = GlobalVariables.FormatTimeToInt(str_cur);
                    string newT = GlobalVariables.FormatTime((int_cur + dayTime).ToString());

                    ybArriveDT.Rows[i]["Cr_offTime"] = newT;
                }
            }
        }


        /// <summary>
        /// 按照夜班的回库顺序给司机排序
        /// </summary>
        /// <param name="ybDT"></param>
        /// <param name="sqlList"></param>
        private void UpdateYbArriverInfo(DataTable ybDT, ref ArrayList sqlList)
        {
            if (null == ybDT || ybDT.Rows.Count < 1)
                return;

            for (int i = 0; i < ybDT.Rows.Count; i++)
            {
                int index = i + 1;
                string sql = "update DriverCombine set dcom_ybarrive = " + index + " where ID = '" + ybDT.Rows[i]["Dcom_ID"].ToString() + "'";

                sqlList.Add(sql);
            }
        }

        private bool refreshSqlList(DataTable roads, DataTable team, ref ArrayList SqlList)
        {
            try
            {
                int count = roads.Rows.Count >= team.Rows.Count ? team.Rows.Count : roads.Rows.Count;   //返回行数少的
                string sql_update = "";
                for (int i = 0; i < count; i++)
                {
                    string roadID = roads.Rows[i]["ID"].ToString();
                    string teamID = team.Rows[i]["DcomID"].ToString();

                    sql_update = "update DriverCombine set dcom_cr_number = '" + roadID + "' where ID = '" + teamID + "'";
                    SqlList.Add(sql_update);
                }
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 返回当前电客车司机组对应班组（早，白，夜，休）
        /// </summary>
        /// <param name="teamID"></param>
        /// <returns></returns>
        public int GetGroupByTeamID(int teamID)
        {
            string sql_getC = "select ctn_cls_id from ClassTurn where ctn_team = " + teamID + " and line = " + line;
            object obj = sqlHelper.GetSingle(sql_getC);

            return obj == null ? -1 : Convert.ToInt32(obj);
        }

        public bool ExangeClass(int team1, int class1, int team2, int class2)
        {
            ArrayList sqlList = new ArrayList();
            string sql_update1 = "update ClassTurn set ctn_cls_id = " + class2 + " where ctn_team = " + team1 + " and line = " + line;
            string sql_update2 = "update ClassTurn set ctn_cls_id = " + class1 + " where ctn_team = " + team2 + " and line = " + line;
            sqlList.Add(sql_update1);
            sqlList.Add(sql_update2);

            return sqlHelper.ExecuteSqlTran(sqlList);
        }
        #region 月计划所有代码
        /// <summary>
        /// 获取一整个月的时刻表数据
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetDtMonthTimetable(int year, int month)
        {

            //string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select TTd.Tableinfo_id,TIF.TableName,TTD.AssignDate" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = " + line + " and TTD.AssignDate like '" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "%'";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            return dt;
        }

        public DataTable GetDtMonthTimetable(string startDay, string endDay)
        {

            //string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select TTd.Tableinfo_id,TIF.TableName,TTD.AssignDate" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = '"+line+"' and TTD.AssignDate between '"+startDay+"' and '"+endDay+"'";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            return dt;
        }

        /// <summary>
        /// 获取这个月所用到的时刻表的id
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetDtMonthTimeTableId(int year, int month)
        {
            string sql_check = "select distinct TableInfo_id ,tif.TableName,tif.ID" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = " + line + " and TTD.AssignDate like '" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "%'";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            return dt;
        }

        //select dcdr_driverCombineId  from DriverCombineCrossingRoadPlane where dcdr_date='' and dcdr_driverCombineId='' and dcdr_team=''

        public DataTable GetDtYBBDriver(string date, string team)
        {
            string sql_check = "select dcdr_driverCombineId  from DriverCombineCrossingRoadPlane where dcdr_date='" + date + "'and dcdr_team='" + team + "' and dcdr_driverType=6";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            return dt;
        }


        public DataTable GetDtMonthTimeTableId(string startDay, string endDay)
        {
            string sql_check = "select distinct TableInfo_id ,tif.TableName,tif.ID" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                "  where TIF.LineID = '" + line + "' and TTD.AssignDate between '" + startDay + "' and '" + endDay + "'";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            return dt;
        }

        /// <summary>
        /// 根据交路id获取整张交路表
        /// </summary>
        /// <param name="tableID"></param>
        /// <returns></returns>
        public DataTable GetDtCrossingRoadByTableId(string tableID)
        {
            string strsql = "select * from CrossingRoad where tableInfo_id = " + tableID + " and isused = 1 order by number";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取每组司机的人数
        /// </summary>
        /// <param name="tableID"></param>
        /// <returns></returns>
        public DataTable GetDtDriverCountByTeam()
        {
            string strsql = "select dcom_team, COUNT(*) as num,dcom_grp_type from DriverCombine where dcom_date is null and dcom_lineid='" + line + "' Group by dcom_team,dcom_grp_type ";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

       

        /// <summary>
        /// 获取最后的轮班记录
        /// </summary>
        /// <returns></returns>
        public DataTable GetLastClass()
        {
            string strsql = "select top 4 * from ClassTurnPlane where line='" + line + "' order by ctn_date desc ";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        public DataTable GetLastClass(string day)
        {
            DateTime dateTime = Convert.ToDateTime(day).AddDays(-1);
            string strsql = "select top 4 * from ClassTurnPlane where line='" + line + "' and ctn_date='"+dateTime.ToString("yyyy-MM-dd")+"'  order by ctn_date desc ";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取当前排班顺序
        /// </summary>
        /// <returns></returns>
        public DataTable GetCurrentClass()
        {
            string strsql = "select * from ClassTurn where line='" + line + "'";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取一张空表来填数据 所以用1>1;
        /// </summary>
        /// <returns></returns>
        public DataTable GetDtClassTrunPlane()
        {
            string strsql = "select  * from ClassTurnPlane where 1>1";
            DataTable dt = sqlHelper.SelectDtAddMissingSchemaAction(strsql);
            return dt;
        }

        public DataTable GetDtDriverCombineCrossingRoadPlane()
        {
            string strsql = "select  * from DriverCombineCrossingRoadPlane where 1>1";
            DataTable dt = sqlHelper.SelectDtAddMissingSchemaAction(strsql);
            return dt;
        }

        /// <summary>
        /// 获取夜班信息(已经不需要初始化，那张空表就可以了)
        /// </summary>
        /// <returns></returns>
        public DataTable GetDtLastybInfo()
        {
            string strsql = "select dcdr_driverCombineId ,dcdr_driverType ,dcdr_team ,dcdr_dutyNum ,dcdr_crossoffTime,dcom_opdriver ,dcom_monidriver ,dcom_monidriverTwo, ' ' as lastTrainTime,' ' as dcom_NO ,dcom_monidriverThree from DriverCombineCrossingRoadPlane where dcdr_date= (select max(ctn_date) from ClassTurnPlane) and 1>1 and dcdr_crosszby=3 and line='" + line + "' order by dcdr_crossoffTime ";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }


        /// <summary>
        /// 获取司机
        /// </summary>
        /// <returns></returns>
        public DataTable GetDtDriverCombine()
        {

            string strsql = "select d.ID,dcom_grp_type,dcom_team ,dcom_dutynum ,dcom_opdriver,dcom_monidriver,dcom_ybarrive,dcom_monidriverTwo,' ' as lastTrainTime,dcom_NO,dcom_monidriverThree,dcom_cr_number,c.offTime as crsOffTime,c.lastTrainTime as crsLastTrainTime,c.attenTime as crsAttenTime from DriverCombine d left join CrossingRoad c on d.dcom_cr_number=c.ID  where  dcom_date is null and dcom_lineid='" + line + "' order by dcom_team, dcom_dutynum ";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取一张空的司机组合表
        /// </summary>
        /// <returns></returns>
        public DataTable GetEmptyDriverCombine()
        {
            string strsql = "select * from DriverCombine where 1>1 order by dcom_team, dcom_dutynum";
            DataTable dt = sqlHelper.SelectDtAddMissingSchemaAction(strsql);
            return dt;
        }


        public void SetDriverCombineCrossingRoadPlane(DataTable dt,string tranId)
        {
            if (string.IsNullOrEmpty(tranId))
                SetDriverCombineCrossingRoadPlane(dt);
            // HelpCom.UpdateSingletable(dt, "DriverCombineCrossingRoadPlane");
            dt.Columns.Remove("ID");
            bool result = true;
            try
            {
                // DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlTransaction tran = sqlHelper.GetTransaction(tranId);
                SqlBulkCopy bcp = new SqlBulkCopy(tran.Connection, SqlBulkCopyOptions.KeepIdentity,tran);
                bcp.DestinationTableName = "DriverCombineCrossingRoadPlane";
                bcp.BatchSize = 100;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(dt);
            }
            catch (System.Exception ex)
            {
                result = false;
            }

        }
        public void SetDriverCombineCrossingRoadPlane(DataTable dt)
        {
            // HelpCom.UpdateSingletable(dt, "DriverCombineCrossingRoadPlane");
            dt.Columns.Remove("ID");
            bool result = true;
            try
            {
                // DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlBulkCopy bcp = new SqlBulkCopy(SqlDataLayer.ConnStr, SqlBulkCopyOptions.KeepIdentity);
                bcp.DestinationTableName = "DriverCombineCrossingRoadPlane";
                bcp.BatchSize = 100;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(dt);
            }
            catch (System.Exception ex)
            {
                result = false;
            }

        }
        public DataTable GetSourceRemarkNew(string sd, string ed) {
            return sqlHelper.SelectDt("select dccrp.id,dccrp.dcdr_userID,dccrp.dcdr_reamrkNew,dccrp.dcdr_date from DriverCombineCrossingRoadPlane as dccrp where dccrp.dcdr_date between '" + sd + "' and '" + ed + "'");
        }

        public void RevertRemarkNew(DataTable dtRemark, DataTable dtDccrp)
        {
            foreach (DataRow dccrpRow in dtDccrp.Rows)
            {
                DataRow[] rows = dtRemark.Select("dcdr_userID=" + dccrpRow["dcdr_userID"] + " and dcdr_date='" + dccrpRow["dcdr_date"]+"'");
                if(rows.Length>0)
                    dccrpRow["dcdr_reamrkNew"] = rows[0]["dcdr_reamrkNew"];
            }
        }

        /// <summary>
        /// 删除计划记录
        /// </summary>
        /// <param name="dt"></param>
        public bool DelPlan(string days)
        {

            return DelPlan(days,null);
        }
        /// <summary>
        /// 删除计划记录
        /// </summary>
        /// <param name="dt"></param>
        public bool DelPlan(string days,string tranId)
        {
            string str = "delete from ClassTurnPlane where ctn_date>='" + days + "' and line='" + line + "';delete from DriverCombineCrossingRoadPlane where dcdr_date>='" + days + "' and line='" + line + "';delete from DriverCombine where dcom_date is not null and dcom_date>='" + days + "' and dcom_lineid='" + line + "';delete from ClassTurnPlaneRecord where re_date >='" + days + "' and line='" + line + "';";
            if (string.IsNullOrEmpty(tranId))
                return sqlHelper.ExecuteSql(str)>0;
            else
                return sqlHelper.ExecuteSql(str,tranId) > 0;
        }

      
        public void SetClassTurnPlane(DataTable dt)
        {
            //HelpCom.UpdateSingletable(dt, "ClassTurnPlane");
            dt.Columns.Remove("ID");
            bool result = true;
            try
            {
                // DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlBulkCopy bcp = new SqlBulkCopy(SqlDataLayer.ConnStr, SqlBulkCopyOptions.KeepIdentity);
                bcp.DestinationTableName = "ClassTurnPlane";
                bcp.BatchSize = 100;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(dt);
            }
            catch (System.Exception ex)
            {
                result = false;
            }
        }

        public void SetClassTurnPlane(DataTable dt,string tranId)
        {
            //HelpCom.UpdateSingletable(dt, "ClassTurnPlane");

            

            dt.Columns.Remove("ID");
            bool result = true;
            try
            {
                SqlTransaction tran = sqlHelper.GetTransaction(tranId);
                // DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlBulkCopy bcp = new SqlBulkCopy(tran.Connection, SqlBulkCopyOptions.KeepIdentity, tran);
                bcp.DestinationTableName = "ClassTurnPlane";
                bcp.BatchSize = 100;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(dt);
            }
            catch (System.Exception ex)
            {
                result = false;
            }
        }
        public void SetDriverCombine(DataTable dt,string tranId)
        {

            if (string.IsNullOrEmpty(tranId))
                SetDriverCombine(dt);
            // HelpCom.UpdateSingletable(dt, "DriverCombine");
            dt.Columns.Remove("ID");
            bool result = true;
            try
            {
                // DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlTransaction tran = sqlHelper.GetTransaction(tranId);
                SqlBulkCopy bcp = new SqlBulkCopy(tran.Connection, SqlBulkCopyOptions.KeepIdentity,tran);
                bcp.DestinationTableName = "DriverCombine";
                bcp.BatchSize = 100;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(dt);
            }
            catch (System.Exception ex)
            {
                result = false;
            }
        }
        public void SetDriverCombine(DataTable dt)
        {
            // HelpCom.UpdateSingletable(dt, "DriverCombine");
            dt.Columns.Remove("ID");
            bool result = true;
            try
            {
                // DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlBulkCopy bcp = new SqlBulkCopy(SqlDataLayer.ConnStr, SqlBulkCopyOptions.KeepIdentity);
                bcp.DestinationTableName = "DriverCombine";
                bcp.BatchSize = 100;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(dt);
            }
            catch (System.Exception ex)
            {
                result = false;
            }
        }
        public bool AddClassTurnPlaneRecord(string startday, string endDay,string tranId)
        {
            string sql_add = "insert into ClassTurnPlaneRecord values('" + startday + "','" + line + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + endDay + "')";
            if (string.IsNullOrEmpty(tranId))
                return sqlHelper.ExecuteSql(sql_add) > 0;
            else
                return sqlHelper.ExecuteSql(sql_add, tranId) > 0;

        }
        public bool AddClassTurnPlaneRecord(string startday,string endDay)
        {
            return AddClassTurnPlaneRecord(startday, endDay, null);
        }
        public bool AddClassTurnPlaneRecord(int year, int month)
        {
            string sql_add = "insert into ClassTurnPlaneRecord values('" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "','" + line + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "')";
            return sqlHelper.ExecuteSql(sql_add) > 0;

        }

        public bool UpdateClassTurnPlaneRecord(string endDay,string tranId)
        {
            DateTime dt = Convert.ToDateTime(endDay);
            endDay = dt.AddDays(-1).ToString("yyyy-MM-dd");
            string sql_add = "update ClassTurnPlaneRecord set endDay='" + endDay + "' where ID=(select top 1 ID from ClassTurnPlaneRecord where line=" + line + " and (CONVERT(dateTime,endDay)>='" + endDay + "' or  endDay is null )order by endDay desc)";
            if (string.IsNullOrEmpty(tranId))

                return sqlHelper.ExecuteSql(sql_add) > 0;
            else
                return sqlHelper.ExecuteSql(sql_add, tranId)>0;

        }

        public bool UpdateClassTurnPlaneRecord(string endDay)
        {
            return UpdateClassTurnPlaneRecord(endDay, null);
        }


        public bool UpdateClassTurnPlaneRecordBySame(string endDay,string tranId)
        {
            DateTime dt = Convert.ToDateTime(endDay);
            // endDay = dt.AddDays(-1).ToString("yyyy-MM-dd");
            string sql_add = " delete from ClassTurnPlaneRecord where ID in(select ID from ClassTurnPlaneRecord where CONVERT(dateTime,re_date) >'"+endDay+"' and line='"+line+"') ; update ClassTurnPlaneRecord set endDay='"+endDay+"' where ID in(select top 1 ID from ClassTurnPlaneRecord where line='"+line+"' order by ID desc  )";

            return sqlHelper.ExecuteSql(sql_add, tranId) > 0;

        }
        public bool UpdateClassTurnPlaneRecordBySame(string endDay)
        {
            return UpdateClassTurnPlaneRecordBySame(endDay, null);
        }

       

        public DataTable GetClassTurnPlaneRecord(int year, int month)
        {
            string strsql = "select * from  ClassTurnPlaneRecord where re_date='" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "'and line='" + line + "'";
            return sqlHelper.SelectDt(strsql);
        }

        public DataTable GetDriverCombineToChkClassPlane()
        {
            string strsql = "select dcom_lineid,dcom_team,dcom_opdriver,dcom_monidriver,dcom_monidriverTwo,dcom_NO,dcom_monidriverThree from DriverCombine where dcom_parents is null and dcom_lineid='" + line + "' order by dcom_lineid,dcom_team,dcom_NO";
            return sqlHelper.SelectDt(strsql);
        }

        public DataTable GetDriverCombineToChkClassPlaneBydate(string date)
        {
            string strsql = "select dcom_lineid,dcom_team,dcom_opdriver,dcom_monidriver,dcom_monidriverTwo,dcom_NO,dcom_monidriverThree from DriverCombine where dcom_date='" + Convert.ToDateTime(date).AddDays(-1) + "' and dcom_lineid='" + line + "' order by dcom_lineid,dcom_team,dcom_NO";
            return sqlHelper.SelectDt(strsql);
        }

        public DataTable GetClassTurnPlaneRecord()
        {
            string strsql = "select re_date + '到'+endDay re_date from  ClassTurnPlaneRecord where line='" + line + "' order by insert_time desc";
            return sqlHelper.SelectDt(strsql);
        }

        public DataTable GetDriverCombineCrossingRoadPlane(int year, int month)
        {

            string strsql = "declare @sql varchar(8000) select @sql = isnull(@sql + '],[' , '') +  CONVERT(varchar(100), dcdr_date, 23) from DriverCombineCrossingRoadPlane where datepart(month,dcdr_date)=" + month + " and datepart(YEAR,dcdr_date)=" + year + " group by dcdr_date order by dcdr_date set @sql = '[' + @sql + ']' declare @sql1 varchar(8000) exec ('SELECT * FROM (SELECT dcdr_team,dcdr_driverCombineId,dcdr_userName ,dcdr_remark,dcdr_date' +' FROM DriverCombineCrossingRoadPlane WHERE line=" + line + "' + ') AS DCRP PIVOT (MAX(dcdr_remark) FOR dcdr_date IN (' + @sql + '))TBL')";
            return sqlHelper.SelectDt(strsql);

        }

        public DataTable GetDriverCombineCrossingRoadPlane(int year, int month, int startDay, int endDay)
        {


            //string strsql = "declare @sql varchar(8000) select @sql = isnull(@sql + '],[' , '') +  CONVERT(varchar(100), dcdr_date, 23) from DriverCombineCrossingRoadPlane where datepart(month,dcdr_date)=" + month + " and datepart(YEAR,dcdr_date)=" + year + " and datepart(day,dcdr_date) between " + startDay + " and " + endDay + " group by dcdr_date order by dcdr_date set @sql = '[' + @sql + ']' declare @sql1 varchar(8000) exec ('SELECT * FROM (SELECT dcdr_team,dcdr_driverCombineId,dcdr_userName ,dcdr_remark,dcdr_date' +' FROM DriverCombineCrossingRoadPlane WHERE line=" + line + "' + ') AS DCRP PIVOT (MAX(dcdr_remark) FOR dcdr_date IN (' + @sql + '))TBL')";
            //string strsql = "exec dbo.pro_Month_plane "+year+" ,"+month+","+startDay+","+endDay+"";
            
            //string strsql = "declare @sql varchar(8000) select @sql = isnull(@sql + '],[' , '') +  CONVERT(varchar(100), dcdr_date, 23) from DriverCombineCrossingRoadPlane where datepart(month,dcdr_date)=" + month + " and datepart(YEAR,dcdr_date)=" + year + " and datepart(day,dcdr_date) between " + startDay + " and " + endDay + " group by dcdr_date order by dcdr_date set @sql = '[' + @sql + ']' declare @sql1 varchar(8000) exec ('SELECT * FROM (SELECT dcdr_team,dcdr_driverCombineId,dcdr_userName ,dcdr_remark,dcdr_date' +' FROM DriverCombineCrossingRoadPlane WHERE line=" + line + "' + ') AS DCRP PIVOT (MAX(dcdr_remark) FOR dcdr_date IN (' + @sql + '))TBL')";

            string strsql = "exec dbo.pro_Month_plane "+year+","+month+","+startDay+","+endDay+"";
            return sqlHelper.SelectDt(strsql);

        }
        public void UpdateDriverCombineCrossingRoadPlaneRemark(Dictionary<string, string> dic)
        {
            StringBuilder sb = new StringBuilder();
            foreach (string key in dic.Keys)
            {
                string[] arr=key.Split('|');
                string userid = arr[0];
                string date = arr[1];
                string remark = dic[key];
                sb.Append(" update DriverCombineCrossingRoadPlane set dcdr_reamrkNew='" + remark + "' where dcdr_userID=" + userid + " and dcdr_date=CONVERT(datetime,'" + date + "'); ");
            }
            if (sb.Length > 0)
            {
                sqlHelper.ExecuteSql(sb.ToString());
            }
            
        }
        public DataTable GetDriverCombineCrossingRoadPlane(string startDay, string endDay)
        {

            string strsql = "exec dbo.pro_Month_plane1 '" + startDay + "','" + endDay + "','"+line+"'";
            return sqlHelper.SelectDt(strsql);

        }
        public DataTable GetDtDriverInfo()
        {
            string strSql = "select user_id, user_name,user_view_name from [system_user] where user_dep_id =7 and user_line = " + line + " order by user_view_name";
            DataTable driverDT = sqlHelper.SelectDt(strSql);
            return driverDT;
        }
        public bool UpdateDriver(string date,string tranId)
        {
            string sql_Update = " update DriverCombine  set  DriverCombine.dcom_dutynum=dc.dcom_dutynum ,DriverCombine.dcom_cr_number=dc.dcom_cr_number,DriverCombine.dcom_ybarrive=dc.dcom_ybarrive ,DriverCombine.dcom_grp_type=dc.dcom_grp_type from (select * from DriverCombine where dcom_date='" + date + "' )  dc where (DriverCombine.dcom_opdriver=dc.dcom_opdriver and DriverCombine.dcom_monidriver=dc.dcom_monidriver and DriverCombine.dcom_date is null) or(DriverCombine.dcom_opdriver=dc.dcom_opdriver and DriverCombine.dcom_date is null and DriverCombine.dcom_monidriver is null and dc.dcom_monidriver is null) or(DriverCombine.dcom_monidriver=dc.dcom_monidriver and DriverCombine.dcom_date is null and DriverCombine.dcom_opdriver is null and dc.dcom_opdriver is null)";
            return sqlHelper.ExecuteSql(sql_Update,tranId) > 0;

        }
        public bool UpdateDriver(string date)
        {
            return UpdateDriver(date, null);

        }

        /// <summary>
        /// 获取最后的轮班时间
        /// </summary>
        /// <returns></returns>
        public string GetLastDate()
        {
            string sql_Update = " select top 1 ctn_date from dbo.ClassTurnPlane order by ctn_date desc";
            DataTable dt = sqlHelper.SelectDt(sql_Update);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }

            return "";
        }
        #endregion

        /// <summary>
        /// 获取所有这个时间段内的调班记录
        /// </summary>
        /// <param name="date"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable GetSwitchRecordByMonth(string startDay, string endday)
        {

            string str = " select s.*,d.line,d.dcdr_userName as appUserName,dc.dcdr_userName as chagUserName from dbo.SwitchedRecord s left join DriverCombineCrossingRoadPlane d on s.sr_applicant =d.dcdr_userID and d.dcdr_date=s.sr_appdate  left join DriverCombineCrossingRoadPlane dc on s.sr_changer =dc.dcdr_userID and dc.dcdr_date=s.sr_appdate where ((s.sr_appdate between'" + startDay + "' and '" + endday + "' and s.parents is null ) or  (s.sr_chgdate between '" + startDay + "'  and '" + endday + "' and s.parents is null)) and d.line='" + line + "' and parents is  null  order by ID desc";

            return sqlHelper.SelectDt(str);
        }

        public int delSwitch(string endday, string tranId)
        {

            string str = "delete from SwitchedRecord where ID in (select s.ID from dbo.SwitchedRecord s left join DriverCombineCrossingRoadPlane d on s.sr_applicant =d.dcdr_userID and d.dcdr_date=s.sr_appdate where (s.sr_appdate >'" + endday + "' )  and d.line='" + line + "')";

            return sqlHelper.ExecuteSql(str, tranId);
        }

        public DataTable GetSwitchRecordByDel(string endday)
        {

            string str = " select s.*,d.line,d.dcdr_userName as appUserName,dc.dcdr_userName as chagUserName from dbo.SwitchedRecord s left join DriverCombineCrossingRoadPlane d on s.sr_applicant =d.dcdr_userID and d.dcdr_date=s.sr_appdate  left join DriverCombineCrossingRoadPlane dc on s.sr_changer =dc.dcdr_userID and dc.dcdr_date=s.sr_appdate where (s.sr_appdate >'" + endday + "' and s.parents is null )  and d.line='" + line + "'  order by ID desc";

            return sqlHelper.SelectDt(str);
        }

    }
}
